﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FileControlOpr
{
    public static class ExcelOpr
    {
        /// <summary>
        /// 从excel读取数据成DataSet
        /// </summary>
        /// <param name="excelPath">文件位置</param>
        /// <param name="hearIndex">表头位置，起始为1</param>
        /// <param name="sheetFlag">多sheet读取</param>
        /// <param name="hasHeadRowFlag">是否读取表头</param>
        /// <returns></returns>
        public static DataSet ExcelToDataTable(string excelPath, int hearIndex = 0, bool sheetFlag = false, bool hasHeadRowFlag = true, int colCnt = 0)
        {
            IWorkbook workbook;

            var extension = Path.GetExtension(excelPath);

            using (FileStream stream = new(excelPath, FileMode.Open, FileAccess.Read))
            {
                if (extension == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }
            }

            var rtnDs = new DataSet();

            var sheetCount = 1;

            if (sheetFlag)
            {
                sheetCount = workbook.NumberOfSheets;
            }

            for (int i = 0; i < sheetCount; i++)
            {
                var sheet = workbook.GetSheetAt(i);
                DataTable dt = SheetToDataTable(sheet, hearIndex, hasHeadRowFlag, colCnt);
                if (dt != null)
                {
                    dt.TableName = sheet.SheetName;
                    rtnDs.Tables.Add(dt.Copy());
                }
            }

            return rtnDs;
        }

        public static DataSet ExcelToDataTableByTiltle(string excelPath, string title, int sheetIndex)
        {

            IWorkbook workbook;

            var extension = Path.GetExtension(excelPath);

            using (FileStream stream = new(excelPath, FileMode.Open, FileAccess.Read))
            {
                if (extension == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }
            }

            var sheetCount = workbook.NumberOfSheets;
            var rtnDs = new DataSet();
            for (int i = 0; i < sheetCount; i++)
            {
                var sheet = workbook.GetSheetAt(i);
                var dts = SheetToDataTable(sheet, title);
                rtnDs.Tables.AddRange(dts.ToArray());
            }
            return rtnDs;
        }

        public static List<DataTable> SheetToDataTable(ISheet sheet, string title)
        {

            var dts = new List<DataTable>();

            var hasData = false;
            DataTable dt = null;
            foreach (IRow row in sheet)
            {
                if (!hasData && row.Cells.Count > 0 && row.Cells[0].StringCellValue.Contains(title))
                {
                    dt = new DataTable();
                    dt.TableName = row.Cells[0].StringCellValue;
                    hasData = true;
                }
                else
                {
                    if (row == null || row.Cells == null || row.Cells.Count <= 0)
                    {
                        dts.Add(dt.Copy());
                        hasData = false;
                    }
                    if (row.Cells.Count > 0 && row.Cells[0].StringCellValue.Contains(title))
                    {
                        dts.Add(dt.Copy());
                        dt = new DataTable();
                        dt.TableName = row.Cells[0].StringCellValue;
                        continue;
                    }

                    if (dt != null && dt.Columns.Count <= 0)
                    {
                        foreach (var item in row.Cells)
                        {
                            dt.Columns.Add(item.StringCellValue);
                        }
                    }
                    else
                    {
                        DataRow dataRow = dt.NewRow();
                        dataRow.ItemArray = row.Cells.FindAll(x => x.ColumnIndex < dt.Columns.Count).Select(c => c.ToString()).ToArray();
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            if (hasData)
            {
                dts.Add(dt.Copy());
            }
            return dts;
        }

        /// <summary>
        /// 单sheet页数据读取成DataTable
        /// </summary>
        /// <param name="sheet">sheet页对象</param>
        /// <param name="hearIndex">表头位置</param>
        /// <param name="hasHeadRowFlag">是否读取表头字段</param>
        /// <returns></returns>
        public static DataTable SheetToDataTable(ISheet sheet, int hearIndex = 0, bool hasHeadRowFlag = true, int colCnt = 0)
        {

            DataTable dt = new();

            IRow headerRow = sheet.GetRow(hearIndex);
            if (headerRow.Cells.Count <= 0)
            {
                return null;
            }

            if (hasHeadRowFlag)
            {
                foreach (var item in headerRow.Cells)
                {
                    if (colCnt > dt.Columns.Count)
                    {
                        dt.Columns.Add(item.StringCellValue);
                    }
                }
                while (colCnt > dt.Columns.Count)
                {
                    dt.Columns.Add(new DataColumn());
                }
            }
            else
            {
                var tIndex = 0;
                foreach (var item in headerRow.Cells)
                {
                    tIndex++;
                    dt.Columns.Add(tIndex.ToString());
                }
            }

            // write the rest
            int rowIndex = 0;
            foreach (IRow row in sheet)
            {
                if (rowIndex++ < hearIndex + 1) continue;

                DataRow dataRow = dt.NewRow();
                dataRow.ItemArray = row.Cells.FindAll(x => x.ColumnIndex < dt.Columns.Count).Select(c => c.ToString()).ToArray();

                //if (string.IsNullOrWhiteSpace(dataRow[1].ToString()))
                //{
                //    continue;
                //}

                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// 根据DataSet导出Excel
        /// </summary>
        /// <typeparam name="T">HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；XSSFWorkbook、SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；</typeparam>
        /// <param name="ds">DataSet</param>
        /// <param name="exportPath">导出文件的文件夹路径</param>
        /// <param name="fileName">文件名(不需要后缀)</param>
        /// <param name="titleFlag">是否使用TableName作为标题显示</param>
        public static void DataSetToExcel<T>(DataSet ds, string exportPath, string fileName,
            bool titleFlag = false,
            Dictionary<int, ICellStyle> titleStyleDic = null,
            Dictionary<int, ICellStyle> contentStyleDic = null) where T : IWorkbook
        {
            IWorkbook workbook = (IWorkbook)Activator.CreateInstance(typeof(T));

            if (titleStyleDic == null) 
                titleStyleDic = GetTiltleStyleDic(workbook);
            if (contentStyleDic == null)
                contentStyleDic = GetContentStyleDic(workbook);

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                ISheet tSheet;
                tSheet = workbook.CreateSheet();
                if (!string.IsNullOrWhiteSpace(dt.TableName))
                {
                    workbook.SetSheetName(i, dt.TableName);
                }

                FillSheet(titleStyleDic, contentStyleDic, tSheet, dt, titleFlag: titleFlag);
            }

            using (MemoryStream ms = new())
            {
                workbook.Write(ms);
                var exportFullPath = Path.Combine(exportPath, fileName)
                    + (typeof(T) == typeof(HSSFWorkbook) ? ".xls" : ".xlsx");

                using (FileStream fs = new(exportFullPath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
                workbook = null;
            }
        }

        /// <summary>
        /// 根据DataSet导出单sheet页Excel
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="exportPath"></param>
        /// <param name="titleFlag"></param>
        public static void DataSetToSignSheetExcel(DataSet ds, string exportPath, int offsetRow = 0, bool titleFlag = false)
        {
            IWorkbook workbook;

            workbook = new XSSFWorkbook();

            var titleStyleDic = GetTiltleStyleDic(workbook);
            var contentStyleDic = GetContentStyleDic(workbook);

            ISheet tSheet;
            tSheet = workbook.CreateSheet();

            int rowCnt = 0;
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];

                FillSheet(titleStyleDic, contentStyleDic, tSheet, dt, offsetRow: rowCnt, titleFlag: titleFlag);
                rowCnt += dt.Rows.Count + (titleFlag ? 2 : 1) + offsetRow;
            }

            using (MemoryStream ms = new())
            {
                workbook.Write(ms);
                using (FileStream fs = new(exportPath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
                workbook = null;
            }
        }

        public static void DataTableToCSV(DataTable dt, string exportPath)
        {

            FileStream fs = new(exportPath, FileMode.OpenOrCreate);
            StreamWriter sw = new(fs, Encoding.Default);

            string colStr = string.Empty;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                colStr += i == 0 ? "" : "," + dt.Columns[i];
            }
            sw.WriteLine(colStr);
            foreach (DataRow item in dt.Rows)
            {
                string rowStr = string.Empty;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    rowStr += i == 0 ? "" : "," + item[i];
                }
                sw.WriteLine(rowStr);
            }
            sw.Close();
        }

        public static void DataSetToExcelByTemplate(DataTable dt, string templFilePath, string exportPath)
        {
            Stream templateStream = new MemoryStream();
            using (var file = new FileStream(templFilePath, FileMode.Open, FileAccess.Read))
            {
                file.CopyTo(templateStream);
                templateStream.Seek(0, SeekOrigin.Begin);
            }

            IWorkbook workbook = new XSSFWorkbook(templateStream);

            //test
            var sheet = workbook.GetSheetAt(0);
            var templRow = sheet.GetRow(0);

            var templParamLst = templRow.Cells.Select(x =>
            {
                if (x.CellType == CellType.Numeric)
                {
                    return new
                    {
                        paramKey = x.NumericCellValue.ToString(),
                        x.ColumnIndex,
                        x.CellStyle
                    };
                }
                else
                {
                    return new
                    {
                        paramKey = x.StringCellValue,
                        x.ColumnIndex,
                        x.CellStyle
                    };
                }
            }).ToList();

            for (int i = 0; i < 6; i++)
            {
                IRow tempRow;
                if (i == 0)
                {
                    tempRow = sheet.GetRow(i);
                }
                else
                {
                    tempRow = sheet.CreateRow(i);
                }

                foreach (var item in templParamLst)
                {

                    var tempCell = tempRow.GetCell(item.ColumnIndex);
                    if (tempCell == null)
                    {
                        tempCell = tempRow.CreateCell(item.ColumnIndex);
                    }
                    tempCell.SetCellValue(new Random().Next(0, 1000) / 1000.0);
                    tempCell.CellStyle = item.CellStyle;
                }
            }

            using (MemoryStream ms = new())
            {
                workbook.Write(ms);
                using (FileStream fs = new(exportPath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
                workbook = null;
            }
        }

        private static Dictionary<int, ICellStyle> GetTiltleStyleDic(IWorkbook workbook)
        {
            var styleDic = new Dictionary<int, ICellStyle>();

            var font1 = workbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.Bold;
            font1.FontName = "等线";
            //左表头样式
            ICellStyle titleLStyle = workbook.CreateCellStyle();
            titleLStyle.BorderBottom = BorderStyle.Medium;
            titleLStyle.BorderLeft = BorderStyle.Medium;
            titleLStyle.BorderRight = BorderStyle.Thin;
            titleLStyle.BorderTop = BorderStyle.Medium;
            titleLStyle.SetFont(font1);
            styleDic.Add(-1, titleLStyle);
            //中表头样式
            ICellStyle titleCStyle = workbook.CreateCellStyle();
            titleCStyle.BorderBottom = BorderStyle.Medium;
            titleCStyle.BorderLeft = BorderStyle.Thin;
            titleCStyle.BorderRight = BorderStyle.Thin;
            titleCStyle.BorderTop = BorderStyle.Medium;
            titleCStyle.SetFont(font1);
            styleDic.Add(0, titleCStyle);
            //右表头样式
            ICellStyle titleRStyle = workbook.CreateCellStyle();
            titleRStyle.BorderBottom = BorderStyle.Medium;
            titleRStyle.BorderLeft = BorderStyle.Thin;
            titleRStyle.BorderRight = BorderStyle.Medium;
            titleRStyle.BorderTop = BorderStyle.Medium;
            titleRStyle.SetFont(font1);
            styleDic.Add(1, titleRStyle);

            return styleDic;
        }

        private static Dictionary<int, ICellStyle> GetContentStyleDic(IWorkbook workbook)
        {
            var styleDic = new Dictionary<int, ICellStyle>();

            var font1 = workbook.CreateFont();
            font1.FontName = "等线";

            ICellStyle cellStyle1 = workbook.CreateCellStyle();
            cellStyle1.BorderBottom = BorderStyle.Thin;
            cellStyle1.BorderLeft = BorderStyle.Medium;
            cellStyle1.BorderRight = BorderStyle.Thin;
            cellStyle1.BorderTop = BorderStyle.Medium;
            cellStyle1.SetFont(font1);
            styleDic.Add(1, cellStyle1);

            ICellStyle cellStyle2 = workbook.CreateCellStyle();
            cellStyle2.BorderBottom = BorderStyle.Thin;
            cellStyle2.BorderLeft = BorderStyle.Thin;
            cellStyle2.BorderRight = BorderStyle.Thin;
            cellStyle2.BorderTop = BorderStyle.Medium;
            cellStyle2.SetFont(font1);
            styleDic.Add(2, cellStyle2);

            ICellStyle cellStyle3 = workbook.CreateCellStyle();
            cellStyle3.BorderBottom = BorderStyle.Thin;
            cellStyle3.BorderLeft = BorderStyle.Thin;
            cellStyle3.BorderRight = BorderStyle.Medium;
            cellStyle3.BorderTop = BorderStyle.Medium;
            cellStyle3.SetFont(font1);
            styleDic.Add(3, cellStyle3);

            ICellStyle cellStyle4 = workbook.CreateCellStyle();
            cellStyle4.BorderBottom = BorderStyle.Thin;
            cellStyle4.BorderLeft = BorderStyle.Medium;
            cellStyle4.BorderRight = BorderStyle.Thin;
            cellStyle4.BorderTop = BorderStyle.Thin;
            cellStyle4.SetFont(font1);
            styleDic.Add(4, cellStyle4);

            ICellStyle cellStyle5 = workbook.CreateCellStyle();
            cellStyle5.BorderBottom = BorderStyle.Thin;
            cellStyle5.BorderLeft = BorderStyle.Thin;
            cellStyle5.BorderRight = BorderStyle.Thin;
            cellStyle5.BorderTop = BorderStyle.Thin;
            cellStyle5.SetFont(font1);
            styleDic.Add(5, cellStyle5);

            ICellStyle cellStyle6 = workbook.CreateCellStyle();
            cellStyle6.BorderBottom = BorderStyle.Thin;
            cellStyle6.BorderLeft = BorderStyle.Thin;
            cellStyle6.BorderRight = BorderStyle.Medium;
            cellStyle6.BorderTop = BorderStyle.Thin;
            cellStyle6.SetFont(font1);
            styleDic.Add(6, cellStyle6);

            ICellStyle cellStyle7 = workbook.CreateCellStyle();
            cellStyle7.BorderBottom = BorderStyle.Medium;
            cellStyle7.BorderLeft = BorderStyle.Medium;
            cellStyle7.BorderRight = BorderStyle.Thin;
            cellStyle7.BorderTop = BorderStyle.Thin;
            cellStyle7.SetFont(font1);
            styleDic.Add(7, cellStyle7);

            ICellStyle cellStyle8 = workbook.CreateCellStyle();
            cellStyle8.BorderBottom = BorderStyle.Medium;
            cellStyle8.BorderLeft = BorderStyle.Thin;
            cellStyle8.BorderRight = BorderStyle.Thin;
            cellStyle8.BorderTop = BorderStyle.Thin;
            cellStyle8.SetFont(font1);
            styleDic.Add(8, cellStyle8);

            ICellStyle cellStyle9 = workbook.CreateCellStyle();
            cellStyle9.BorderBottom = BorderStyle.Medium;
            cellStyle9.BorderLeft = BorderStyle.Thin;
            cellStyle9.BorderRight = BorderStyle.Medium;
            cellStyle9.BorderTop = BorderStyle.Thin;
            cellStyle9.SetFont(font1);
            styleDic.Add(9, cellStyle9);

            return styleDic;
        }

        private static void FillSheet(Dictionary<int, ICellStyle> titleStyles, Dictionary<int, ICellStyle> styles, ISheet tSheet, DataTable dt,
            int offsetCol = 0, int offsetRow = 0, bool titleFlag = false)
        {
            //初始行数
            var initRowIndex = titleFlag ? (offsetRow + 1) : (offsetRow + 0);

            if (titleFlag)
            {
                IRow titleRow = tSheet.GetRow(offsetRow);
                if (titleRow == null)
                {
                    titleRow = tSheet.CreateRow(offsetRow);
                }

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = titleRow.GetCell(offsetCol + j);
                    if (cell == null)
                    {
                        cell = titleRow.CreateCell(offsetCol + j);
                    }
                    if (j == 0)
                    {
                        cell.CellStyle = titleStyles[-1];
                    }
                    else if (j == dt.Columns.Count - 1)
                    {
                        cell.CellStyle = titleStyles[1];
                    }
                    else
                    {
                        cell.CellStyle = titleStyles[0];
                    }
                    cell.SetCellValue(dt.TableName);
                }

                var mergedRegion = new CellRangeAddress(offsetRow, offsetRow, titleRow.FirstCellNum, titleRow.LastCellNum - 1);
                tSheet.AddMergedRegion(mergedRegion);
            }

            //添加表头
            IRow headerRow = tSheet.GetRow(initRowIndex);
            if (headerRow == null)
            {
                headerRow = tSheet.CreateRow(initRowIndex);
            }
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                var cell = headerRow.GetCell(offsetCol + j);
                if (cell == null)
                {
                    cell = headerRow.CreateCell(offsetCol + j);
                }
                if (j == 0)
                {
                    cell.CellStyle = titleStyles[-1];
                }
                else if (j == dt.Columns.Count - 1)
                {
                    cell.CellStyle = titleStyles[1];
                }
                else
                {
                    cell.CellStyle = titleStyles[0];
                }
                cell.SetCellValue(dt.Columns[j].ToString());
            }

            //循环添加每一行数据
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                IRow tRow = tSheet.GetRow(initRowIndex + j + 1);
                if (tRow == null)
                {
                    tRow = tSheet.CreateRow(initRowIndex + j + 1);
                }

                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    var cell = tRow.GetCell(offsetCol + k);
                    if (cell == null)
                    {
                        cell = tRow.CreateCell(offsetCol + k);
                    }

                    if (j == 0)
                    {
                        if (k == 0)
                        {
                            cell.CellStyle = styles[1];
                        }
                        else if (k == dt.Columns.Count - 1)
                        {
                            cell.CellStyle = styles[3];
                        }
                        else
                        {
                            cell.CellStyle = styles[2];
                        }
                    }
                    else if (j == dt.Rows.Count - 1)
                    {
                        if (k == 0)
                        {
                            cell.CellStyle = styles[7];
                        }
                        else if (k == dt.Columns.Count - 1)
                        {
                            cell.CellStyle = styles[9];
                        }
                        else
                        {
                            cell.CellStyle = styles[8];
                        }
                    }
                    else
                    {
                        if (k == 0)
                        {
                            cell.CellStyle = styles[4];
                        }
                        else if (k == dt.Columns.Count - 1)
                        {
                            cell.CellStyle = styles[6];
                        }
                        else
                        {
                            cell.CellStyle = styles[5];
                        }
                    }

                    var cellVal = dt.Rows[j][k].ToString();
                    cell.SetCellValue(cellVal);
                }
            }

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                tSheet.AutoSizeColumn(offsetCol + j);
            }
        }

        //public static void AddOther(DataTable dt, string excelPath, int offset)
        //{
        //    IWorkbook workbook;
        //    using (FileStream stream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
        //    {
        //        workbook = new XSSFWorkbook(stream);
        //    }

        //    ICellStyle titleStyle = workbook.CreateCellStyle();
        //    titleStyle.BorderBottom = BorderStyle.Medium;
        //    titleStyle.BorderLeft = BorderStyle.Thin;
        //    titleStyle.BorderRight = BorderStyle.Thin;
        //    titleStyle.BorderTop = BorderStyle.Medium;
        //    var font1 = workbook.CreateFont();
        //    font1.Boldweight = (short)FontBoldWeight.Bold;
        //    font1.FontName = "等线";
        //    titleStyle.SetFont(font1);

        //    ICellStyle style = workbook.CreateCellStyle();
        //    style.BorderBottom = BorderStyle.Thin;
        //    style.BorderLeft = BorderStyle.Thin;
        //    style.BorderRight = BorderStyle.Thin;
        //    style.BorderTop = BorderStyle.Thin;
        //    var font2 = workbook.CreateFont();
        //    font2.FontName = "等线";
        //    style.SetFont(font2);

        //    var sheetCount = workbook.Count();

        //    for (int i = 0; i < sheetCount; i++)
        //    {
        //        ISheet sheet = workbook.GetSheetAt(i);

        //        FillSheet(titleStyle, style, sheet, dt, offset);
        //    }

        //    using (MemoryStream ms = new MemoryStream())
        //    {
        //        workbook.Write(ms);
        //        using (FileStream fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write))
        //        {
        //            byte[] data = ms.ToArray();
        //            fs.Write(data, 0, data.Length);
        //            fs.Flush();
        //        }
        //        workbook = null;
        //    }
        //}

        //public static void DataSetToSignExcel(DataSet ds, string exportPath, DataTable oDt = null)
        //{
        //    for (int i = 0; i < ds.Tables.Count; i++)
        //    {
        //        IWorkbook workbook;

        //        workbook = new XSSFWorkbook();

        //        ICellStyle titleStyle = workbook.CreateCellStyle();
        //        titleStyle.BorderBottom = BorderStyle.Medium;
        //        titleStyle.BorderLeft = BorderStyle.Thin;
        //        titleStyle.BorderRight = BorderStyle.Thin;
        //        titleStyle.BorderTop = BorderStyle.Medium;
        //        var font1 = workbook.CreateFont();
        //        font1.Boldweight = (short)FontBoldWeight.Bold;
        //        font1.FontName = "等线";
        //        titleStyle.SetFont(font1);

        //        ICellStyle style = workbook.CreateCellStyle();
        //        style.BorderBottom = BorderStyle.Thin;
        //        style.BorderLeft = BorderStyle.Thin;
        //        style.BorderRight = BorderStyle.Thin;
        //        style.BorderTop = BorderStyle.Thin;
        //        var font2 = workbook.CreateFont();
        //        font2.FontName = "等线";
        //        style.SetFont(font2);

        //        DataTable dt = ds.Tables[i];
        //        ISheet tSheet;
        //        tSheet = workbook.CreateSheet();

        //        if (!string.IsNullOrWhiteSpace(dt.TableName))
        //        {
        //            workbook.SetSheetName(0, dt.TableName);
        //        }

        //        FillSheet(titleStyle, style, tSheet, dt);
        //        if (oDt != null)
        //        {
        //            FillSheet(titleStyle, style, tSheet, oDt, 9);
        //        }

        //        using (MemoryStream ms = new MemoryStream())
        //        {
        //            workbook.Write(ms);
        //            using (FileStream fs = new FileStream(exportPath + dt.TableName + ".xlsx", FileMode.Create, FileAccess.Write))
        //            {
        //                byte[] data = ms.ToArray();
        //                fs.Write(data, 0, data.Length);
        //                fs.Flush();
        //            }
        //            workbook = null;
        //        }
        //    }
        //}
    }
}
